
/**
 *
 */
package com.serviceprovider.business.module.admin.transaction.services.dao.impl;

//~--- non-JDK imports --------------------------------------------------------

import com.serviceprovider.business.module.admin.transaction.model.NewCsvReplica;
import com.serviceprovider.business.module.admin.transaction.model.TransactionTarget;
import com.serviceprovider.business.module.admin.transaction.services.dao.interfaces.ITransactionDataService;
import com.serviceprovider.business.module.admin.transaction.utility.StringUtils;
import com.serviceprovider.business.module.admin.transaction.valueobjects.TransactionCSVHistory;
import com.serviceprovider.business.module.admin.user.services.dao.interfaces.IUserService;
import com.serviceprovider.business.module.sp.transaction.model.TransactionDetail;
import com.serviceprovider.business.module.sp.transaction.services.dao.interfaces.ITransStatusDao;
import com.serviceprovider.business.module.sp.transaction.services.interfaces.ITransactionService;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

//~--- JDK imports ------------------------------------------------------------

import java.io.UnsupportedEncodingException;

import java.net.URLDecoder;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author mrduongnv@gmail.com
 *
 */
@Service
public class TransactionDataServiceImpl extends JdbcDaoSupport implements ITransactionDataService {

    /** Logger for this class and subclasses */
    protected final Log         logger = LogFactory.getLog(getClass());
    @Autowired
    private ITransactionService transactionService;
    @Autowired
    ITransStatusDao             transactionStatusDao;
    @Autowired
    IUserService                userService;

    @Autowired
    public TransactionDataServiceImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    @Override
    public boolean isDuplicatedRecord(String uniqueFieldValue, String uniqueDbField) {
        String sql = "select " + uniqueDbField + " from sp_target_transformation where " + uniqueDbField + " ="
                     + uniqueFieldValue;
        List dataList = getJdbcTemplate().queryForList(sql);

        return !((dataList == null) || dataList.isEmpty());
    }

    @Override
    public void loadTransactionRecord(List<String> sqlList, List<String> updateList) {
        for (String sql : sqlList) {
            getJdbcTemplate().update(sql);
        }

        for (String sql : updateList) {
            getJdbcTemplate().update(sql);
        }
    }

    @Override
    @Transactional
    public void loadTransactionDataFromTable() {
        String sql =
            "INSERT INTO `sp_transaction_details` ( `user_id` , `transaction_amount` , `transaction_status_id` , `service_fee` , `self_fee` , `receiver_name` , `originator_name` , `trace_number` )"
            + "SELECT b.original_id, transaction_amount, 1, 0, 0, receiver_name, originator_name, trace_number "
            + "FROM sp_Target_Transformation a RIGHT JOIN sp_id_mappings b ON b.mapped_id = a.receiver_account where processed=0";

        System.out.println(sql);
        getJdbcTemplate().update(sql);
        getJdbcTemplate().update("Update sp_Target_Transformation set processed=1 where processed=0");
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void insertTransactionHistory(TransactionCSVHistory csvHistory) {
        if (csvHistory == null) {
            throw new IllegalArgumentException("TransactionCSVHistory is null");
        }

        final String sql =
            "insert into sp_import_transactions_history (file_name, path, user_name, created_date, has_error, processed) values(?,?,?,?,?,?)";

        getJdbcTemplate().update(sql, new Object[] {
            csvHistory.getFileName(), csvHistory.getPath(), csvHistory.getUserName(), csvHistory.getCreatedDate(),
            csvHistory.isHasError(), csvHistory.isProcessed()
        });
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void markAsProcessedTransactionHistory(Integer csvHistoryId) {
        if (csvHistoryId == null) {
            throw new IllegalArgumentException("TransactionCSVHistory id is null");
        }

        String sql = "update sp_import_transactions_history set has_error = ?, modified_date = ? where id = ?";

        getJdbcTemplate().update(sql, new Object[] { Boolean.FALSE, new Date(), csvHistoryId });
    }

    /**
     * {@inheritDoc}
     *
     */
    @Override
    public List<TransactionCSVHistory> retrieveTransactionCsvHistoryRecords(String username)
            throws UnsupportedEncodingException {
        if (StringUtils.isEmpty(username)) {
            throw new IllegalArgumentException("username is empty.");
        }

        String                      sql         = "SELECT * FROM sp_import_transactions_history where user_name = ?";
        final List                  list        = getJdbcTemplate().queryForList(sql, new Object[] { username });
        List<TransactionCSVHistory> historyList = new ArrayList<TransactionCSVHistory>();
        Map                         row         = null;
        TransactionCSVHistory       history     = null;

        for (Object obj : list) {
            row     = (Map) obj;
            history = new TransactionCSVHistory();
            history.setId(new Integer(row.get("id").toString()));
            history.setFileName((String) row.get("file_name"));
            history.setPath(URLDecoder.decode((String) row.get("path"), "UTF-8"));
            history.setUserName((String) row.get("user_name"));
            history.setCreatedDate((Date) row.get("created_date"));
            history.setModifiedDate((row.get("modified_date") == null)
                                    ? null
                                    : (Date) row.get("modified_date"));
            history.setHasError((Boolean) row.get("has_error"));
            history.setProcessed((Boolean) row.get("processed"));
            historyList.add(history);
        }

        return historyList;
    }

    /**
     * {@inheritDoc}
     *
     *
     */
    @Override
    public List<String> getAllNonMatchingUserIds(List<NewCsvReplica> results) {
        List<String> idList = new ArrayList<String>();
        String       sql    = "SELECT distinct(mapped_id) FROM sp_id_mappings";

        if ((results == null) || results.isEmpty()) {
            return null;
        }

        Map               row      = null;
        List              list     = getJdbcTemplate().queryForList(sql);
        ArrayList<String> dataList = new ArrayList<String>();

        for (Object obj : list) {
            row = (Map) obj;
            dataList.add((String) row.get("mapped_id"));
        }

        for (NewCsvReplica newCsvReplica : results) {
            if (!dataList.contains(newCsvReplica.getService_provider_id())) {
                idList.add(newCsvReplica.getService_provider_id());
            }
        }

        return idList;
    }

    /**
     * {@inheritDoc}
     *
     *
     */
    @Override
    public TransactionCSVHistory retrieveTransactionCsvHistory(String destination) throws UnsupportedEncodingException {
        if (StringUtils.isEmpty(destination)) {
            throw new IllegalArgumentException("destination is empty.");
        }

        String sql = "SELECT * FROM sp_import_transactions_history where path = ?";

        System.out.println("Path=" + destination);

        final List list = getJdbcTemplate().queryForList(sql, new Object[] { destination });

        if ((list == null) || list.isEmpty()) {
            return null;
        }

        Map                   row     = null;
        TransactionCSVHistory history = null;
        Object                obj     = list.get(0);

        row     = (Map) obj;
        history = new TransactionCSVHistory();
        history.setId(new Integer(row.get("id").toString()));
        history.setFileName((String) row.get("file_name"));
        history.setPath(URLDecoder.decode((String) row.get("path"), "UTF-8"));
        history.setUserName((String) row.get("user_name"));
        history.setCreatedDate((Date) row.get("created_date"));
        history.setModifiedDate((row.get("modified_date") == null)
                                ? null
                                : (Date) row.get("user_name"));
        history.setHasError((Boolean) row.get("has_error"));
        history.setProcessed((Boolean) row.get("processed"));

        return history;
    }

    @Override
    public List<TransactionTarget> retrieveTransformedData() {
        List<TransactionTarget> targetList = new ArrayList<TransactionTarget>();
        String                  sql        = "SELECT * FROM sp_target_transformation where processed = false";
        final List              list       = getJdbcTemplate().queryForList(sql);
        Map                     row        = null;
        TransactionTarget       target     = null;

        for (Object obj : list) {
            row    = (Map) obj;
            target = new TransactionTarget();

            if (row.get("id") != null) {
                target.setTransaction_id(new Integer(row.get("id").toString()));
            }

            if (row.get("user_id") != null) {
                target.setUser_id(new Integer(row.get("user_id").toString()));
            }

            if (row.get("customer_id") != null) {
                target.setCustomer_id(new Integer(row.get("customer_id").toString()));
            }

            if (row.get("transaction_amount") != null) {
                target.setTransaction_amount(new Integer(row.get("transaction_amount").toString()));
            }

            if (row.get("transaction_status_id") != null) {
                target.setTransaction_status_id(new Integer(row.get("transaction_status_id").toString()));
            }

//          if (row.get("service_fee") != null) {
//                  target.setService_fee(new Integer(row.get("service_fee").toString()));
//          }
//          if (row.get("self_fee") != null) {
//                  target.setSelf_fee(new Integer(row.get("self_fee").toString()));
//          }
//          if (row.get("tax_prep_fee") != null) {
//                  target.setTax_prep_fee(new Integer(row.get("tax_prep_fee").toString()));
//          }
//          if (row.get("electronic_prep_fee") != null) {
//                  target.setElectronic_prep_fee(new Integer(row.get("electronic_prep_fee").toString()));
//          }
//          if (row.get("electronic_filling_fee") != null) {
//                  target.setElectronic_filling_fee(new Integer(row.get("electronic_filling_fee").toString()));
//          }
//          if (row.get("service_bureau_fee") != null) {
//                  target.setService_bureau_fee(new Integer(row.get("service_bureau_fee").toString()));
//          }
//          if (row.get("fed_bank_product_fee") != null) {
//                  target.setFed_bank_product_fee(new Integer(row.get("fed_bank_product_fee").toString()));
//          }
//          if (row.get("state_product_fee") != null) {
//                  target.setState_product_fee(new Integer(row.get("state_product_fee").toString()));
//          }
//          if (row.get("custom_label_1_value") != null) {
//                  target.setCustom_label_1_value(new Integer(row.get("custom_label_1_value").toString()));
//          }
//          if (row.get("custom_label_2_value") != null) {
//                  target.setCustom_label_2_value(new Integer(row.get("custom_label_2_value").toString()));
//          }
            if (row.get("customer_balance") != null) {
                target.setCustomer_balance(new Integer(row.get("customer_balance").toString()));
            }

//          if (row.get("payee_1_amount") != null) {
//                  target.setPayee_1_amount(new Integer(row.get("payee_1_amount").toString()));
//          }
//          if (row.get("payee_2_amount") != null) {
//                  target.setPayee_2_amount(new Integer(row.get("payee_2_amount").toString()));
//          }
//          if (row.get("payee_3_amount") != null) {
//                  target.setPayee_3_amount(new Integer(row.get("payee_3_amount").toString()));
//          }
//          if (row.get("payee_4_amount") != null) {
//                  target.setPayee_4_amount(new Integer(row.get("payee_4_amount").toString()));
//          }
//          if (row.get("batch_id") != null) {
//                  target.setBatch_id(new Integer(row.get("batch_id").toString()));
//          }
            if (row.get("data_load_date") != null) {
                target.setData_load_date((Date) row.get("data_load_date"));
            }

            if (row.get("receiver_name") != null) {
                target.setReceiver_name(row.get("receiver_name").toString());
            }

            if (row.get("originator_name") != null) {
                target.setOriginator_name(row.get("originator_name").toString());
            }

            if (row.get("customer_ssn") != null) {
                target.setCustomer_ssn(new Long(row.get("customer_ssn").toString()));
            }

            if (row.get("trace_number") != null) {
                target.setTrace_number(row.get("trace_number").toString());
            }

            target.setProcessed((Boolean) row.get("processed"));
            targetList.add(target);
        }

        return targetList;
    }

    @Override
    public void insertTransactionDetails(List<TransactionTarget> targetDate) {
        if ((targetDate != null) &&!targetDate.isEmpty()) {
            for (TransactionTarget transactionTarget : targetDate) {
                TransactionDetail transaction = new TransactionDetail();

                transaction.setAmount(transactionTarget.getTransaction_amount());
                transaction.setUserId(transactionTarget.getUser_id());
                transaction.setCustomerBalance(transactionTarget.getCustomer_balance());
                transaction.setTransactionStatus(
                    transactionStatusDao.get(transactionTarget.getTransaction_status_id()));
                transaction.setDataLoadDate(transactionTarget.getData_load_date());
                transaction.setOriginatorName(transactionTarget.getOriginator_name());

//              builder.append(("'" + transactionTarget.getReceiver_name() + "'"));
                transaction.setCustomerSSN(transactionTarget.getCustomer_ssn().intValue());
                transaction.setTraceNumber(transactionTarget.getTrace_number());
                transactionService.insertTransaction(transaction);
            }
        }
    }
}



